package com.union.core.job.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hnjowe.webdb.DBOperation;
import com.union.core.complain.model.Complain;
import com.union.core.job.dao.DailyJobDao;
import com.union.core.job.model.DailyJob;
import com.union.core.sys.dao.impl.SysManagerDaoImpl;
import com.union.core.sys.service.SysService;

public class DailyJobDaoImpl implements DailyJobDao {
	private static Logger log = Logger.getLogger(SysManagerDaoImpl.class
			.getName());
	private DBOperation opera;
	private SysService sysService = SysService.getInstance();
	public DailyJobDaoImpl(){
		
	}
	@Override
	public boolean addJob(DailyJob job) {
		
		boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			StringBuffer sqlBuf = new StringBuffer();
			sqlBuf.append("insert into daily_job");
			sqlBuf.append("(job_name,job_type,job_details,ORGANIZATION_ID,");
			sqlBuf.append(" job_file,status,create_time,staff_id,update_staff,update_time,staff_name,organization_name,file_path,receive_list) ");
			sqlBuf.append("values(?,?,?,?,?,?,SYSDATE(),?,?,SYSDATE(),?,?,?,?);");
			opera.prepStatment(sqlBuf.toString());
			opera.setString(1, job.getJOB_NAME());
			opera.setInt(2, job.getJOB_TYPE());
			opera.setString(3, job.getJOB_DETAILS());
			opera.setInt(4, sysService.getSessionListener().getPERSONNEL().getOrganization_id());
			opera.setString(5, job.getJOB_FILE());
			opera.setInt(6, 1);
			opera.setInt(7, sysService.getSessionListener().getPERSONNEL_ID());
			opera.setString(8, sysService.getSessionListener().getPERSONNEL().getPersonnel_name());
			opera.setString(9, job.getStaff_name());
			opera.setString(10, job.getOrganization_name());
			opera.setString(11, job.getFile_path());
			opera.setString(12, job.getReceive_list());
			 opera.execute();
			result = opera.transcationCommit();
			if(result == false)
			{
				log.error("sql="+opera.getPres().toString());
				return false;
			}
		} catch (Exception e) {
			log.error(e);
			log.error("sql="+opera.getPres().toString());
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	@Override
	public DailyJob getJobById(int jobId) {
		DailyJob job = new DailyJob();
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select "
					+ "job_id,job_name,job_type,job_details,job_file,staff_id," 
					+ "create_time,organization_id,status,update_staff,update_time," 
					+ "organization_name,staff_name,file_path,receive_list" 
					+" from daily_job where job_id="+jobId;
			opera.prepStatment(sql);
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				
				job.setJOB_ID(res.getInt(1));
				job.setJOB_NAME(res.getString(2));
				job.setJOB_TYPE(res.getInt(3));
				job.setJOB_DETAILS(res.getString(4));
				job.setJOB_FILE(res.getString(5));
				job.setSTAFF_ID(res.getInt(6));
				job.setCREATE_TIME(res.getString(7));
				job.setORGANIZATION_ID(res.getInt(8));
				job.setSTATUS(res.getInt(9));
				job.setUPDATE_STAFF(res.getString(10));
				job.setUPDATE_TIME(res.getString(11));
				job.setOrganization_name(res.getString(12));
				job.setStaff_name(res.getString(13));
				job.setFile_path(res.getString(14));
				job.setReceive_list(res.getString(15));
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}	
		return job;
	}

	@Override
	public List<DailyJob> getJobList(String startDate, String endDate,
			int status, int orgId, int staffId, String queryType,
			String queryData,String orderByName, int start, int PageSize) {
		String sqlstr1 = ""; 	//条件1
		String sqlstr2 = ""; 	//条件2
		String sqlstr3 = ""; 	//条件3
		String sqlstr4 = ""; 	//条件4
		String sqlstr5 = ""; 	//条件5
		String sqlstr6 = ""; 	//条件6
		String sqlstr7 = ""; 	//条件7
		String sqlstr8 = ""; 	//条件8
		String sqlstr9 = ""; 	//条件9
		String sqlstr10 = ""; 	//条件10
		String orderbySql = ""; //排序条件
		if(orgId!=0){
			sqlstr1=" and receive_list like '%,"+orgId+",%'" ;
		}
		if (null!=startDate && !"null".equals(startDate) &&!"".equals(startDate) ) {
			sqlstr2=" and create_time >  DATE_FORMAT('"+startDate+"','%Y~%m~%d %k.%i.%s' )";
		}
		if (null!=endDate && !"null".equals(endDate) &&!"".equals(endDate) ) {
			sqlstr3=" and create_time <  DATE_FORMAT('"+endDate+"','%Y~%m~%d %k.%i.%s' )";
		}
		if (queryType != null && queryData != null) {
			if(queryType.equals("organization_name")) sqlstr7 = " and organization_name like '%"+queryData+"%'";
			if(queryType.equals("job_name")) sqlstr7 = " and job_name like '%"+queryData+"%'";
			if(queryType.equals("staff_name")) sqlstr7 = " and staff_name like '%"+queryData+"%'";
			
		}
		if (orderByName != null&& !"".equals(orderByName)) {
			String str[] = orderByName.split("-");			
			if (str[1].equals("UP"))
				orderbySql = " order by " + str[0];
			if (str[1].equals("DOWN"))
				orderbySql = " order by " + str[0] + " desc";
		} else {
			orderbySql = " order by job_id desc";
		}
		List<DailyJob> jobList = new ArrayList<DailyJob>();
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select "
					+ "job_id,job_name,job_type,job_details,job_file,staff_id," 
					+ "create_time,organization_id,status,update_staff,update_time," 
					+ "organization_name,staff_name,file_path,receive_list" 
					+" from daily_job where job_id>0" + sqlstr1 + sqlstr2 + sqlstr3 + sqlstr4 + sqlstr5+ sqlstr6 
					+ sqlstr7 + sqlstr8 + sqlstr9 + sqlstr10 +orderbySql  
			        + " LIMIT ?,?";
			opera.prepStatment(sql);
			 opera.setInt(1, start);
			 opera.setInt(2, PageSize);
			ResultSet res = opera.executeQuery();
			int i = 0;
			while (res.next()) {
				DailyJob job = new DailyJob();
				job.setJOB_ID(res.getInt(1));
				job.setJOB_NAME(res.getString(2));
				job.setJOB_TYPE(res.getInt(3));
				job.setJOB_DETAILS(res.getString(4));
				job.setJOB_FILE(res.getString(5));
				job.setSTAFF_ID(res.getInt(6));
				job.setCREATE_TIME(res.getString(7));
				job.setORGANIZATION_ID(res.getInt(8));
				job.setSTATUS(res.getInt(9));
				job.setUPDATE_STAFF(res.getString(10));
				job.setUPDATE_TIME(res.getString(11));
				job.setOrganization_name(res.getString(12));
				job.setStaff_name(res.getString(13));
				job.setFile_path(res.getString(14));
				job.setReceive_list(res.getString(15));
				jobList.add(i, job);
				i++;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}	
		return jobList;
	}

	@Override
	public int getJobListCount(String startDate, String endDate, int status,
			int orgId, int staffId, String queryType,
			String queryData) {
		String sqlstr1 = ""; 	//条件1
		String sqlstr2 = ""; 	//条件2
		String sqlstr3 = ""; 	//条件3
		String sqlstr4 = ""; 	//条件4
		String sqlstr5 = ""; 	//条件5
		String sqlstr6 = ""; 	//条件6
		String sqlstr7 = ""; 	//条件7
		String sqlstr8 = ""; 	//条件8
		String sqlstr9 = ""; 	//条件9
		String sqlstr10 = ""; 	//条件10
		int count=0;
		if(orgId!=0){
			sqlstr1=" and receive_list like '%,"+orgId+",%'" ;
		}
		if (null!=startDate && !"null".equals(startDate) &&!"".equals(startDate) ) {
			sqlstr2=" and create_time >  DATE_FORMAT('"+startDate+"','%Y~%m~%d %k.%i.%s' )";
		}
		if (null!=endDate && !"null".equals(endDate) &&!"".equals(endDate) ) {
			sqlstr3=" and create_time <  DATE_FORMAT('"+endDate+"','%Y~%m~%d %k.%i.%s' )";
		}
		if (queryType != null && queryData != null) {
			if(queryType.equals("organization_name")) sqlstr7 = " and organization_name like '%"+queryData+"%'";
			if(queryType.equals("job_name")) sqlstr7 = " and job_name like '%"+queryData+"%'";
			if(queryType.equals("staff_name")) sqlstr7 = " and staff_name like '%"+queryData+"%'";
		}
		
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select "
					+ "count(*)" 
					+" from daily_job where job_id>0" + sqlstr1 + sqlstr2 + sqlstr3 + sqlstr4 + sqlstr5+ sqlstr6 
					+ sqlstr7 + sqlstr8 + sqlstr9 + sqlstr10;  
			opera.prepStatment(sql);
			ResultSet res = opera.executeQuery();
			
			if (res.next()) {
				count=res.getInt(1);
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}	
		return count;
	}

	@Override
	public boolean editJob(DailyJob job) {
		// TODO Auto-generated method stub
		return false;
	}
	
}
